#!/usr/bin/env python3
#coding:utf-8
'''
Created on 2018-06-22

@author: 许瑞锐

@email：1549359145@qq.com
'''
import pymysql,time,string
class DB():
    def __init__(self,user='root',password='root',dbname='douban',port=3306,host='localhost'):
        self.host=host
        self.user=user
        self.pwd=password
        self.dbname=dbname
        self.port=port

        if self.connect () is False:
            print(self.dbname+"数据库连接异常！\n")
            return None
        print('数据库连接成功！')
    def connect(self):
        try:
            conn=pymysql.connect(host=self.host,user=self.user,password=self.pwd,db=self.dbname,port=self.port,use_unicode=True, charset="utf8")
            self.conn=conn
            self.cursor=conn.cursor()
        except:
            return False
        return True
        
    def IsIdExists(self,id,tablename):
        sql='select * from '+tablename+' where id='+str(id)
        self.cursor.execute(sql)
        self.conn.commit()
        result=self.cursor.fetchone() 
        if result is None:
            return False
        return True
    
    def InserInfos(self,dataSet):
        for data in dataSet:
            if(self.IsIdExists(data['id'],'book_info')==True):
                print('该书籍已经收集过，自动忽略')
                pass
            self.InsertAInfo(data)
            
    def InsertAInfo(self,data):
        try:
            sql=("insert into book_info (id,name,author,price,intro,rate_num,vote_num,img_url) values ('%s','%s','%s','%s','%s','%s','%s','%s')"%(data['id'],data['name'],data['author'],data['price'],data['intro'],data['rate_num'],data['vote_num'],data['img_url']))
            self.cursor.execute(sql)
            self.conn.commit()
            tags=data['tags']
            i=0
            sql="insert into book_tags (book_id,content) values"
            sqlStr=""
            for tag in tags:
                if i==0:
                    sqlStr=("('%s','%s')"%(data['id'],tag))
                else:
                    sqlStr=sqlStr+','+("('%s','%s')"%(data['id'],tag))
                i=i+1    
            sql=sql+sqlStr
            self.cursor.execute(sql)
            self.conn.commit()
            comments=data['comment']
            sql="insert into book_comments (book_id,content) values"
            i=0
            sqlStr=""
            for com in comments:
                if i==0:
                    sqlStr=("('%s','%s')"%(data['id'],com))
                else:
                    sqlStr=sqlStr+','+("('%s','%s')"%(data['id'],com))
                i=i+1  
            sql=sql+sqlStr
            print(sql)   
            self.cursor.execute(sql)
            self.conn.commit()
        except:
            print('数据库写入出错，操作回滚')
            self.conn.rollback()
            self.conn.commit()
            return
        print('数据库写入成功')
                       